﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;

namespace TicketsPhone.Domain.Access
{
    /// <summary>
    /// Represents the interaction with the sql server data base
    /// implements the IRepository interfaz
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ConexionSql<T> : IConsultasBase<T>
    {
        //Represents the generic name of stored procedure
        string storedprocedure;

        //Represents a sql-transact instruction in the sql server data base
        private SqlCommand Comando = null;

        public bool Transaccion { get; set; }

        //Represents a connection to sql server data base
        public SqlConnection connection { get; set; }

        //Represents a transaction to sql server data base
        public SqlTransaction tr { get; set; }

        //provides a way to read a data stream
        public SqlDataReader Reader { get; set; }

        public TipoErrorSql TipoError { get; set; }
        public string Error { get; set; }
        

        public T Objeto { get; set; }

        public ConexionSql() {
            connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TicketsPhoneConnectionString"].ConnectionString);
        }
        
        public ConexionSql(string stored)
        {
            storedprocedure = stored;
            connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TicketsPhoneConnectionString"].ConnectionString);
        }

        public ConexionSql(string stored, string strconnection)
        {
            storedprocedure = stored;
            connection = new SqlConnection(strconnection);
        }

        protected void CrearComando(string consulta)
        {
            CrearComando(consulta, true);
        }

        protected void CrearComando(string consulta, bool esProcedure)
        {
            TipoError = TipoErrorSql.None;
            Comando = new SqlCommand()
            {
                CommandText = consulta,
                CommandType = esProcedure ? CommandType.StoredProcedure : CommandType.Text,
                CommandTimeout = connection.ConnectionTimeout,
                Connection = connection
            };

            if (Transaccion)
            {
                Open();
                if (tr == null)
                    tr = connection.BeginTransaction(IsolationLevel.ReadCommitted);

                Comando.Transaction = tr;
            }
        }

        //protected void AgregarParametro(PropertyInfo propiedad, string valor)
        //{ 
        //    TipoDatoSql db = (Attribute.GetCustomAttribute(propiedad, typeof(TipoDatoSql)) as TipoDatoSql);
        //    CrearComando(storedprocedure, true);
        //    AgregarParametro("@"+db.Nombre, valor, ParameterDirection.Input, db.Tipo);
        //}
        
        protected void AgregarParametro(string nombre, object valor, ParameterDirection direccion, DbType tipo)
        {
            Comando.Parameters.Add(new SqlParameter()
            {
                DbType = tipo,
                Direction = direccion,
                ParameterName = nombre,
                Value = valor
            });
        }

        protected IDataRecord GetDataReader()
        {
            return Reader;
        }

        public int ExecuteNonQuery()
        {
            try
            {
                Open();
                return Comando.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                if (ex.Number == 2627)
                {
                    TipoError = TipoErrorSql.Unique;
                    return 0;
                }
                else if(ex.Number == 547)
                {
                    TipoError = TipoErrorSql.ReferenceConstraint;
                    Error = ex.ToString();
                    return 0;
                }
                else
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Transaccion)
                    Close();
            }
        }

        public object ExecuteScalar()
        {
            try
            {
                Open();
                return Comando.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                if (ex.Number == 2627)
                {
                    TipoError = TipoErrorSql.Unique;
                    return 0;
                }
                else
                {
                    throw ex;
                }
            }
            finally
            {
                if (Transaccion)
                    Close();
            }
        }

        public void ExecuteReader()
        {
            try
            {
                Open();
                if (Transaccion)
                {
                    Reader = Comando.ExecuteReader();
                }
                else
                {
                    Reader = Comando.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                Close();
                throw ex;
            }
        }

        public bool Read()
        {
            if (Reader.Read())
            {
                return true;
            }
            else
            {
                if (!Transaccion)
                {
                    Close();
                }
            }
            return false;
        }

        public object GetValue(string name)
        {
            return Reader[name];
        }

        public object GetValue(int index)
        {
            return Reader[index];
        }

        public int GetIntValue(string name)
        {
            return Convert.ToInt32(Reader[name]);
        }
        
        public string GetStringValue(string name)
        {
            return Reader[name].ToString();
        }

        public bool GetBooleanValue(string name)
        {
            return Convert.ToBoolean( Reader[name]);
        }

        public DateTime GetDateValue(string name)
        {
            return Convert.ToDateTime(Reader[name]);
        }

        public TicketsPhone.Domain.Utils.Hora GetTimeValue(string name)
        {
            return new Utils.Hora((TimeSpan)Reader[name]);
        }

        public void Commit()
        {
            if (tr != null)
                tr.Commit();
        }

        public void RollBack()
        {
            if (tr != null)
                tr.Rollback();
        }

        public void Open()
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        public void Close()
        {
            if (connection != null)
                connection.Close();
        }

        void Grouptransaction(params ConexionSql<T>[] objects)
        {
            Transaccion = true;
            Open();
            tr = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            foreach (ConexionSql<T> item in objects)
            {
                item.Transaccion = true;
                item.connection = connection;
                item.tr = tr;
            }
        }

        //////public object Insertar()
        //////{
        //////    CargarParametros("_Insertar");
        //////    return ExecuteScalar();
        //////}

        //////public void Actualizar()
        //////{
        //////    CargarParametros("_Actualizar");
        //////    ExecuteNonQuery();
        //////}

        //////public void Eliminar()
        //////{
        //////    CargarParametros("_Eliminar");
        //////    ExecuteNonQuery();
        //////}

        //////public virtual void Cargar()
        //////{
        //////    CargarParametros("_Cargar");
        //////    ExecuteReader();
        //////}

        //////public virtual List<T> ObtenerTodos()
        //////{
        //////    //CargarParametros("_Obtener");
        //////    //ExecuteReader();

        //////    //while (Read())
        //////    //{
        //////    //    (GetDataReader());
        //////    //}
        //////    return new List<T>();
        //////}

        protected void CargarParametros(string procedimiento)
        {
            CrearComando(procedimiento, true);
            

            foreach (PropertyInfo item in typeof(T).GetProperties())
            {
                TipoDatoSql db = (Attribute.GetCustomAttribute(item, typeof(TipoDatoSql)) as TipoDatoSql);

                if (item.GetValue(Objeto, null) == null)
                {
                    AgregarParametro(db.Nombre, item.GetValue(Objeto, null), ParameterDirection.Input, db.Tipo);    
                }                
            }
        }


    }
}
